<%@ page contentType="text/html;charset=UTF-8" language="java"%>
<%@ page language="java" trimDirectiveWhitespaces="true"%>
<%@ page import="java.util.*"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@ page import="org.apache.commons.lang3.StringUtils"%>
<%@ page import="org.apache.commons.lang.StringEscapeUtils"%>
<%@ page import="org.apache.poi.ss.usermodel.*,org.apache.poi.hssf.usermodel.*,org.apache.poi.hssf.util.HSSFColor,org.apache.poi.ss.util.CellRangeAddress"%>
<%@ page import="cn.dy.workatten.pojo.*"%>
<%@ page import="cn.dy.workatten.dao.*"%>
<%@ page import="cn.dy.base.framework.esb.def.RepMessage"%>
<%@ page import="cn.dy.base.framework.esb.call.imp.JsonServiceProxy"%>
<%@ page import="java.text.DecimalFormat"%>
											
<%
	response.setHeader("Pragma", "No-Cache");
	response.setHeader("Cache-Control", "No-Cache");
	response.setDateHeader("Expires", 0);  
	
	
    
	// 获得参数
    String dept_id = request.getParameter("dept_id");
    String monthStr = request.getParameter("monthStr");
	String searchStr = java.net.URLDecoder.decode(request.getParameter("searchStr"));
     
    RepMessage rep = new RepMessage();
    JsonServiceProxy<RepMessage> service= new JsonServiceProxy<RepMessage>("cn.dy.workatten.service.DutyAllowanceService",RepMessage.class);
    service.setParam("dept_id", dept_id);
    service.setParam("monthStr", monthStr);
    service.setParam("searchStr", searchStr);
	service.setParam("pageNum", 0);
	service.setParam("pageSize", 0);
	rep = service.call("queryDutyAllowance");
    Map content=(Map)rep.getContent();
    List<DutyScoreSummary> dutyScoreList = new ArrayList<DutyScoreSummary>();
	if(content!=null && content.get("result")!=null){
		Map resultMap = (Map)content.get("result");
		if(resultMap.get("list") != null){
			dutyScoreList = (List<DutyScoreSummary>)resultMap.get("list");
		}
	}

	int year = Integer.parseInt(monthStr.substring(0, 4));
	int month = Integer.parseInt(monthStr.substring(5, 7));

	String titleStr = year + " 年 "+ month +" 月加班补贴（工作量）情况表";

	String report_name = year + "年"+ month +"月加班补贴（工作量）情况表";
	String report_range = "";  
	
    response.setContentType("application/x-msdownload;charset=utf-8");
    response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(report_name, "UTF-8") + ".xls");

    
    Date date = new Date();
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    String dateString = formatter.format(date);    
  	HSSFWorkbook wb = new HSSFWorkbook();
  	HSSFSheet sheet = wb.createSheet("sheet1");//创建Excel工作表对象    
  	
  	//主标题字体
    HSSFFont titleFont = wb.createFont();
  	titleFont.setFontName("宋体");
  	titleFont.setFontHeightInPoints((short) 28);//字号      
  	titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
    titleFont.setUnderline(HSSFFont.U_SINGLE);
    
    //副标题字体
    HSSFFont subtitleFont = wb.createFont();
    subtitleFont.setFontName("宋体");
    subtitleFont.setFontHeightInPoints((short) 14);//字号      
    subtitleFont.setColor(HSSFColor.GREY_50_PERCENT.index);
    
    //表格头字体
    HSSFFont headFont = wb.createFont();
    headFont.setFontName("宋体");
    headFont.setFontHeightInPoints((short) 9);//字号      
    headFont.setColor(HSSFColor.DARK_BLUE.index);
    headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    
    //表格体字体
    HSSFFont bodyFont = wb.createFont();
    bodyFont.setFontName("宋体");
    bodyFont.setFontHeightInPoints((short) 9);//字号      
    
    //表格体字体（红）
    HSSFFont bodyFontRed = wb.createFont();
    bodyFontRed.setFontName("宋体");
    bodyFontRed.setFontHeightInPoints((short) 9);//字号      
    bodyFontRed.setColor(HSSFColor.RED.index);
    
    //表格体字体（粗）
    HSSFFont bodyFontBold = wb.createFont();
    bodyFontBold.setFontName("宋体");
    bodyFontBold.setFontHeightInPoints((short) 9);//字号      
    bodyFontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  	
  	HSSFCellStyle title_style = wb.createCellStyle();    
    title_style.setFont(titleFont);
  	title_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    title_style.setWrapText(true);
    title_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  	title_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框       
  	title_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框       
  	title_style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框       
  	title_style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
    
  	HSSFCellStyle subtitle_style = wb.createCellStyle();    
  	subtitle_style.cloneStyleFrom(title_style);
  	subtitle_style.setFont(subtitleFont);
    
  	HSSFCellStyle head_style = wb.createCellStyle();    
  	head_style.cloneStyleFrom(title_style);
  	head_style.setFont(headFont);
    
  	HSSFCellStyle body_style = wb.createCellStyle();    
  	body_style.cloneStyleFrom(title_style);
  	body_style.setFont(bodyFont);
    
    HSSFCellStyle body_red_style = wb.createCellStyle();    
    body_red_style.cloneStyleFrom(title_style);
    body_red_style.setFont(bodyFontRed);
    
    HSSFCellStyle foot_style = wb.createCellStyle();    
    foot_style.setFont(bodyFontBold);    
    
    String boundState="";
   	// 行头
	String[] aColumnHead = { "序号", "姓名" ,"身份证号", "部门", "周一至周五法定工作日(系数1）", "周一至周五加值班（系数1.5）", "周末加值班（系数2）", "与法定节假日相连假日值班（系数2.5）",
			"法定节假日加值班（不含春节系数3）", "春节假日加值班（系数4）", "额外分值", "0点后加班分值" ,"总分值", "加班分值（总分值-法定分值）", "加值班费"};
	String[] bColumnHead = { "早中晚副班", "16小时值班", "早中晚副班", "副班", "24小时值班", "早中晚副班", "副班", "24小时值班", "早中晚副班", "副班", "24小时值班", "早中晚副班", "副班", "24小时值班"  };
	String[] cColumnHead = { "天数", "分数", "天数", "分数","天数", "分数",   "天数", "分数", "天数", "分数","天数", "分数", "天数", "分数", "天数", "分数","天数", "分数",
			"天数", "分数", "天数", "分数","天数", "分数", "天数", "分数", "天数", "分数","天数", "分数",};
			
		HSSFRow row = null;
		HSSFCell cell = null;
		int row_num = 4;
		HSSFRow titleRow = sheet.createRow(0); // 标题行
		// HSSFRow subtitleRow = sheet.createRow(1); // 副标
		DecimalFormat df = new DecimalFormat("0.00");
		try {
			for (int i = 0; i < dutyScoreList.size(); i++) {
				DutyScoreSummary oneDutyScore = dutyScoreList.get(i);
				row = sheet.createRow(row_num++);
				cell = row.createCell(0);
				cell.setCellStyle(body_style);
				cell.setCellValue((i+1));// 序号
				
				cell = row.createCell(1);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getStaffName());// 姓名


				cell = row.createCell(2);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getIdcard());// 身份证


				cell = row.createCell(3);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getDeptName());// 部门

				cell = row.createCell(4);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_1_day());// 天数

				cell = row.createCell(5);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_1_score());// 分数

				cell = row.createCell(6);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_2_day());// 天数
				cell = row.createCell(7);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_2_score());// 分数

				cell = row.createCell(8);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_3_day());// 天数
				cell = row.createCell(9);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_3_score());// 分数

				cell = row.createCell(10);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_4_day());// 天数
				cell = row.createCell(11);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_4_score());// 分数

				cell = row.createCell(12);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_5_day());// 天数
				cell = row.createCell(13);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_5_score());// 分数

				cell = row.createCell(14);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_6_day());// 天数
				cell = row.createCell(15);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_6_score());// 分数

				cell = row.createCell(16);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_7_day());// 天数
				cell = row.createCell(17);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_7_score());// 分数


				cell = row.createCell(18);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_8_day());// 天数
				cell = row.createCell(19);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_8_score());// 分数


				cell = row.createCell(20);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_9_day());// 天数
				cell = row.createCell(21);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_9_score());// 分数

				cell = row.createCell(22);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_10_day());// 天数
				cell = row.createCell(23);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_10_score());// 分数

				cell = row.createCell(24);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_11_day());// 天数
				cell = row.createCell(25);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_11_score());// 分数

				cell = row.createCell(26);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_12_day());// 天数
				cell = row.createCell(27);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_12_score());// 分数

				cell = row.createCell(28);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_13_day());// 天数
				cell = row.createCell(29);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_13_score());// 分数

				cell = row.createCell(30);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_14_day());// 天数
				cell = row.createCell(31);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_14_score());// 分数

				cell = row.createCell(32);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_15_day());// 天数
				cell = row.createCell(33);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_15_score());// 分数


				cell = row.createCell(34);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getSchedule_16_score());// 额外分值

				cell = row.createCell(35);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getMidnight_score());// 0点后加班分值

				cell = row.createCell(36);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getAll_score());// 总分值


				cell = row.createCell(37);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getOvertime_score());// 加班分值（总分值-法定分值）


				cell = row.createCell(38);
				cell.setCellStyle(body_style);
				cell.setCellValue(oneDutyScore.getOvertime_pay());// 加班费
				
			}
		  
			
		
			// 设置标表头
			row = sheet.createRow(1);
			sheet.createRow(2);
			sheet.createRow(3);
			row.setHeightInPoints(9 * 2 + 4);// 字太长，两行高
			
			// 初始化表头的样式
			for (int i = 0; i < 39; i++) {
				sheet.getRow(0).createCell(i).setCellStyle(title_style);
				sheet.getRow(1).createCell(i).setCellStyle(head_style);
				sheet.getRow(2).createCell(i).setCellStyle(head_style);
				sheet.getRow(3).createCell(i).setCellStyle(head_style);
			}
			
			for (int i = 0; i < aColumnHead.length; i++) {
				sheet.getRow(0).createCell(i).setCellStyle(title_style);
				
				if(i < 5) {
					cell = row.createCell(i);
				} if(i == 5) {
					cell = row.createCell(i + 1);
				} else if(i > 5 && i < 10) {
					int cellIndex = 10 + ((i - 6) * 6);
					cell = row.createCell(cellIndex);
				} else if(i >= 10){
					cell = row.createCell(i + 24);
				}
				
				cell.setCellValue(aColumnHead[i]);
				cell.setCellStyle(head_style);
//					sheet.autoSizeColumn(i, true);// 先写数据，后标题，以便一次性调整宽度
//					if (sheet.getColumnWidth(i) < 253 * 256) {// 最大为255*256宽
//						sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 512);// 汉字调整后总小1-2个字符，256为一个字符宽
//					} else {
//						sheet.setColumnWidth(i, 255 * 256);
//					}
			}
			
			row = sheet.getRow(2);
			for (int i = 0; i < bColumnHead.length; i++) {
				//添加表头内容
				int cellIndex = i + 6 + i;
				HSSFCell headCell = row.createCell(cellIndex);
				headCell.setCellValue(bColumnHead[i]);
				headCell.setCellStyle(head_style);
//		            sheet.autoSizeColumn(i, true);// 先写数据，后标题，以便一次性调整宽度
//					if (sheet.getColumnWidth(i) < 253 * 256) {// 最大为255*256宽
//						sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 512);// 汉字调整后总小1-2个字符，256为一个字符宽
//					} else {
//						sheet.setColumnWidth(i, 255 * 256);
//					}
			}
			
			row = sheet.getRow(3);
			for (int i = 0; i < cColumnHead.length; i++) {
				//添加表头内容
				HSSFCell headCell = row.createCell(i + 4);
				headCell.setCellValue(cColumnHead[i]);
				headCell.setCellStyle(head_style);
//		            sheet.autoSizeColumn(i, true);// 先写数据，后标题，以便一次性调整宽度
//					if (sheet.getColumnWidth(i) < 253 * 256) {// 最大为255*256宽
//						sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 512);// 汉字调整后总小1-2个字符，256为一个字符宽
//					} else {
//						sheet.setColumnWidth(i, 255 * 256);
//					}
			}
			
			List<Integer[]> list3 = new ArrayList(); //要合并的行和列
			Integer[] array1 = {1, 3, 0, 0}; 
			Integer[] array2 = {1, 3, 1, 1};
			Integer[] array3 = {1, 3, 2, 2};
			Integer[] array4 = {1, 3, 3, 3};
			Integer[] array5 = {1, 2, 4, 5};
			Integer[] array6 = {1, 1, 6, 9};
			Integer[] array7 = {1, 1, 10, 15};
			Integer[] array8 = {1, 1, 16, 21};
			Integer[] array9 = {1, 1, 22, 27};
			Integer[] array10 = {1, 1, 28, 33};
			Integer[] array11 = {1, 3, 34, 34};
			Integer[] array12 = {1, 3, 35, 35};
			Integer[] array13 = {1, 3, 36, 36};
			Integer[] array14 = {1, 3, 37, 37};
			Integer[] array15 = {1, 3, 38, 38};
			Integer[] array16 = {2, 2, 6, 7};
			Integer[] array17 = {2, 2, 8, 9};
			Integer[] array18 = {2, 2, 10, 11};
			Integer[] array19 = {2, 2, 12, 13};
			Integer[] array20 = {2, 2, 14, 15};
			Integer[] array21 = {2, 2, 16, 17};
			Integer[] array22 = {2, 2, 18, 19};
			Integer[] array23 = {2, 2, 20, 21};
			Integer[] array24 = {2, 2, 22, 23};
			Integer[] array25 = {2, 2, 24, 25};
			Integer[] array26 = {2, 2, 26, 27};
			Integer[] array27 = {2, 2, 28, 29};
			Integer[] array28 = {2, 2, 30, 31};
			Integer[] array29 = {2, 2, 32, 33};
			
			
			list3.add(array1);
			list3.add(array2);
			list3.add(array3);
			list3.add(array4);
			list3.add(array5);
			list3.add(array6);
			list3.add(array7);
			list3.add(array8);
			list3.add(array9);
			list3.add(array10);
			list3.add(array11);
			list3.add(array12);
			list3.add(array13);
			list3.add(array14);
			list3.add(array15);
			list3.add(array16);
			list3.add(array17);
			list3.add(array18);
			list3.add(array19);
			list3.add(array20);
			list3.add(array21);
			list3.add(array22);
			list3.add(array23);
			list3.add(array24);
			list3.add(array25);
			list3.add(array26);
			list3.add(array27);
			list3.add(array28);
			list3.add(array29);
			
			for (Integer[] arr : list3) {//合并表头
				sheet.addMergedRegion(new CellRangeAddress(arr[0], arr[1], arr[2], arr[3])); 
			}


			// 设置标题
			titleRow.setHeightInPoints(28 + 4);
			cell = titleRow.getCell(0);// 创建Excel工作表指定行的单元格
			cell.setCellStyle(title_style);
			cell.setCellValue(titleStr); // 设置Excel工作表的值
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,38));
		} catch (Exception e1) {
			e1.printStackTrace();
			System.out.println("导出excel异常:"+e1);
		}
	    out.clear();//清空前面的空格，否则会报错
	    out=pageContext.pushBody();
	    wb.write(response.getOutputStream());//写入输出流中
%>


<!DOCTYPE html>
<html class="no-js">

<head>
	<title>导出</title>
</head>

<body class="mainbody">

	<form method="post" id="infoForm" name="infoForm" enctype="multipart/form- data">
	   
	</form>

</body>

</html>